package com.example.children.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.children.DTO.AdminSelect;
import com.example.children.Model.Student;
import com.example.children.DTO.StudentClassDTO;
import com.example.children.DTO.TeacherSelect;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * StudentMapper 接口
 */
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    // 这里可以定义一些自定义查询方法，但常规的增、删、改、查操作不需要手动实现


//    @Select("SELECT s.*, c.class_name FROM student s LEFT JOIN class c ON s.class_id = c.id")
    // 自定义分页查询
@Select("SELECT s.id as id, s.name as name, s.gender,date_format(s.birthday,'%Y-%m-%d') as birthday, s.parent_name as parentName, " +
        "s.parent_phone as parentPhone,date_format(s.enrollment_date,'%Y-%m-%d') as enrollment_date, s.photo_url as photoUrl, " +
        "ct.class_name as className, c.class_id as classId, t.teacher_id as teacherId " +
        "FROM student s " +
        "LEFT JOIN class c ON s.id = c.student_id " +
        "left JOIN classTo ct ON ct.class_id = c.class_id " +
        "left JOIN classteacher t ON t.class_id = c.class_id " +
        "WHERE t.teacher_id = #{teacherId}")
Page<StudentClassDTO> selectStudentWithClassPage(Page<StudentClassDTO> page, String teacherId);



    @Select("select * from student where id=#{id}")
    Student selectToId(String id);


    @Select("SELECT " +
            "s.id as id, s.name as name, s.gender, " +
            "DATE_FORMAT(s.birthday, '%Y-%m-%d') as birthday, " +
            "s.parent_name as parentName, " +
            "s.parent_phone as parentPhone, " +
            "DATE_FORMAT(s.enrollment_date, '%Y-%m-%d') as enrollmentDate, " +
            "s.photo_url as photoUrl " +
            "FROM student s " +
            "WHERE s.id IS NOT NULL " +
            "ORDER BY s.enrollment_date " +
            "LIMIT #{page.offset}, #{page.size}")
    Page<StudentClassDTO> getStudentWithClassPageAdmin(Page<StudentClassDTO> page);




    // 查询总记录数
    @Select("SELECT COUNT(*) FROM student s WHERE s.id IS NOT NULL")
    int countTotalRecords();

    // 手动分页查询
    @Select("SELECT " +
            "s.id as id, s.name as name, s.gender, " +
            "DATE_FORMAT(s.birthday, '%Y-%m-%d') as birthday, " +
            "s.parent_name as parentName, " +
            "s.parent_phone as parentPhone, " +
            "DATE_FORMAT(s.enrollment_date, '%Y-%m-%d') as enrollmentDate, " +
            "s.photo_url as photoUrl, " +
            "t.teacher_name as teacherName, t.teacher_phone as teacherPhone,ct.class_name as className " +
            "FROM student s " +
            "LEFT JOIN class c ON s.id = c.student_id " +  // 使用 LEFT JOIN 确保所有学生都被查询
            "LEFT JOIN classTo ct ON ct.class_id = c.class_id " +
            "LEFT JOIN classteacher cls ON cls.class_id = c.class_id " +  // 使用 LEFT JOIN 确保班主任信息也被返回
            "LEFT JOIN teacher t ON t.id = cls.teacher_id " +  // 使用 LEFT JOIN 获取教师信息
            "WHERE s.id IS NOT NULL " +  // 确保查询所有学生
            "AND (cls.headteacher = '班主任' OR cls.headteacher IS NULL) " +  // 只查询班主任或没有班主任的记录
            "ORDER BY s.enrollment_date " +  // 按照入学日期排序
            "LIMIT #{pageSize} OFFSET #{offset}")  // 分页参数
    List<StudentClassDTO> getStudentWithClassPageAdminSHOUDONG(@Param("offset") int offset, @Param("pageSize") int pageSize);












    //查询学生入园日期
@Select("SELECT * FROM Student WHERE enrollment_date LIKE CONCAT('%', #{enrollmentDate}, '%') ORDER BY enrollment_date")
    List<Student> selectByDateSend(Student student);



    //多条件查询  AND teacherName LIKE CONCAT('%', #{teacher}, '%')
    @Select("SELECT test.*,date_format(birthday,'%Y-%m-%d') as birthday " +
            "FROM ( " +
            "    SELECT * " +
            "    FROM student " +
            "    WHERE (#{ageEnd} IS NULL OR birthday <= #{ageEnd}) " +
            "      AND (#{ageStart} IS NULL OR birthday >= #{ageStart}) " +
            "      AND (#{gender} IS NULL OR gender = #{gender}) " +
            "      AND (#{name} IS NULL OR name LIKE CONCAT('%', #{name}, '%')) " +
            ") AS test "+
            "LEFT JOIN class on test.id=class.student_id " +
            "LEFT JOIN classteacher on classteacher.class_id=class.class_id " +
            "WHERE classteacher.teacher_id = #{teacherId}"
    )
    List<Student> findStudents(TeacherSelect teacherSelect);

    //管理员条件查询
    @Select("SELECT test.*, DATE_FORMAT(test.birthday, '%Y-%m-%d') AS birthday " +
            "FROM ( " +
            "    SELECT * " +
            "    FROM student " +
            "    WHERE " +
            "        (#{ageEnd} IS NULL OR birthday <= #{ageEnd}) " +   // 处理结束年龄
            "        AND (#{ageStart} IS NULL OR birthday >= #{ageStart}) " + // 处理开始年龄
            "        AND (#{gender} IS NULL OR gender = #{gender}) " +   // 处理性别
            "        AND (#{name} IS NULL OR name LIKE CONCAT('%', #{name}, '%')) " + // 姓名模糊查询
            ") AS test " +
            "LEFT JOIN class ON test.id = class.student_id " +  // 连接班级表
            "LEFT JOIN classteacher ON classteacher.class_id = class.class_id " +  // 连接班级老师表
            "LEFT JOIN teacher ON teacher.id = classteacher.teacher_id " +  // 连接老师表
            "WHERE #{teacherName} is null or teacher.teacher_name LIKE CONCAT('%', #{teacherName}, '%')"  // 根据老师名字过滤
    )
    List<Student> findStudentsAdmin(AdminSelect adminSelect);







    //    查询所有学生和班级信息
    @Select("SELECT " +
            "s.id as studentId, s.name as studentName, s.gender, " +
            "date_format(s.birthday, '%Y-%m-%d') as birthday, " +
            "s.class_id as classId, s.parent_name as parentName, " +
            "s.parent_phone as parentPhone, " +
            "date_format(s.enrollment_date, '%Y-%m-%d') as enrollmentDate, " +
            "s.photo_url as photoUrl, " +
            "ct.class_name as className, c.teacher_id as teacherId, " +
            "t.teacher_name as teacherName, t.teacher_phone as teacherPhone " +
            "FROM student s " +
            "LEFT JOIN class c ON s.id = c.student_id " +
            "left JOIN classTo ct ON ct.class_id = c.class_id " +
            "LEFT JOIN teacher t ON c.teacher_id = t.id " +
            "WHERE s.id IS NOT NULL " +
            "ORDER BY enrollment_date")
    List<StudentClassDTO> selectStudentClassDTO();






}
